# encoding: utf-8
"""
@ Time: 2025/6/2 16:54
@ Autor:
@ File: app.py
"""


from flask import Flask, render_template, send_file, jsonify
import pandas as pd
import numpy as np
from collections import Counter
from pyecharts.charts import Geo, Map3D, Sankey, Graph, Page, Bar, Line, Pie, HeatMap, Gauge, Timeline,Boxplot
from pyecharts import options as opts
from pyecharts.globals import ChartType, ThemeType
from geopy.geocoders import Nominatim
from geopy.extra.rate_limiter import RateLimiter
import json
from pyecharts.commons.utils import JsCode
import warnings
warnings.filterwarnings('ignore')

app = Flask(__name__)

# 读取数据和地理编码
df = pd.read_excel('24年10-12月航空数据.xlsx')
with open('city_coords.json', encoding='utf-8') as f:
    city_coords = json.load(f)

# 1.动态航线图
from pyecharts import options as opts
from pyecharts.charts import Geo, Timeline
from pyecharts.globals import ChartType
from pyecharts.options import InitOpts
import pandas as pd
import json

def flight_dynamic_map(df, city_coords):
    df_routes = df[['出发城市', '到达城市']].dropna().drop_duplicates()
    origins = df_routes['出发城市'].unique().tolist()

    # 控制线条粗细和飞机大小
    line_width = 2
    plane_size = line_width * 6  # 这里设置为线宽的6倍

    plane_svg = (
        "path://M896.5,512L409.5,195.5L386,200l-6.5,50.5l-21.5,167H171"
        "c-12.7,0-23,10.3-23,23v52c0,12.7,10.3,23,23,23h187l21.5,167"
        "l6.5,50.5L409.5,832L896.5,515z"
    )

    tl = Timeline(init_opts=InitOpts(
        bg_color="rgba(135, 206, 235, 0.3)"
    ))

    for origin in origins:
        geo = Geo(init_opts=InitOpts(bg_color="rgba(135, 206, 235, 0.3)")) \
            .add_schema(
                maptype="china",
                itemstyle_opts=opts.ItemStyleOpts(
                    area_color="rgba(230, 247, 255, 0.7)",
                    border_color="#999999",
                    border_width=0.8
                )
            )

        # 注册城市坐标
        for city, (lng, lat) in city_coords.items():
            geo.add_coordinate(city, lng, lat)

        dsts = df_routes[df_routes['出发城市'] == origin]['到达城市'].tolist()
        lines = [(origin, dest) for dest in dsts]

        # 航线：虚线、无箭头、调细
        geo.add(
            series_name="航线",
            data_pair=lines,
            type_=ChartType.LINES,
            effect_opts=opts.EffectOpts(
                period=4,
                trail_length=0.3,
                symbol=plane_svg,
                symbol_size=plane_size  # 飞机大小与线粗细成正比
            ),
            linestyle_opts=opts.LineStyleOpts(
                curve=0.2,
                opacity=0.7,
                width=line_width,     # 变细
                type_="dashed",       # 虚线
                color="#FF4500"
            )
        )

        # 出发点：红实心＋红字
        geo.add(
            series_name="出发",
            data_pair=[(origin, 1)],
            type_=ChartType.EFFECT_SCATTER,
            symbol_size=12,
            itemstyle_opts=opts.ItemStyleOpts(color="#FF0000"),
            label_opts=opts.LabelOpts(
                is_show=True,
                formatter="{b}",
                position="right",
                color="#FF0000",   # 改成红字
                font_size=16
            )
        )

        # 到达点：红空心＋黑字
        geo.add(
            series_name="到达",
            data_pair=[(dest, 1) for dest in dsts],
            type_=ChartType.EFFECT_SCATTER,
            symbol_size=12,
            itemstyle_opts=opts.ItemStyleOpts(
                color="rgba(0,0,0,0)",
                border_color="#FF0000",
                border_width=2
            ),
            label_opts=opts.LabelOpts(
                is_show=True,
                formatter="{b}",
                position="right",
                color="#000000",
                font_size=16
            )
        )

        geo.set_global_opts(
            title_opts=opts.TitleOpts(
                title=f"{origin} → 共 {len(dsts)} 条航线",
                title_textstyle_opts=opts.TextStyleOpts(
                    color="#000000",   # 标题改成黑色
                    font_size=20       # 放大标题字体
                ),
                pos_left="5%",         # 可以微调位置
                pos_top="5%"
            ),
            legend_opts=opts.LegendOpts(is_show=False),
            tooltip_opts=opts.TooltipOpts(
                trigger="item",
                background_color="rgba(50,50,50,0.6)",  # 半透明度提高
                border_width=0,
                padding=3,  # 内边距减小
                textstyle_opts=opts.TextStyleOpts(
                    color="#fff",
                    font_size=15  # 字体变小
                ),
                extra_css_text=(
                    "max-width:150px;"  # 限制最大宽度
                    "max-height:70px;"  # 限制最大高度
                    "overflow-y:auto;"  # 超出时显示垂直滚动
                    "box-shadow: 0 1px 4px rgba(0,0,0,0.25);"  # 投影更轻
                    "border-radius:4px;"  # 微圆角
                )
            )
        )
        tl.add(geo, origin)

    tl.add_schema(is_auto_play=True, play_interval=4000, is_loop_play=True)
    return tl

# 2.每日航班量前十动态图
def daily_top10_timeline(df):
    df['起飞时间'] = pd.to_datetime(df['起飞时间']).dt.date
    grp = df.groupby(['起飞时间', '航空公司']).size().reset_index(name='count')
    tl = Timeline(init_opts=opts.InitOpts(
            bg_color="rgba(0,0,0,0)"  # 可选：设置背景透明
        ))
    tl.add_schema(is_auto_play=True, play_interval=4500, is_loop_play=True)
    for date, group in grp.groupby('起飞时间'):
        top10 = group.sort_values('count', ascending=False).head(10)
        airlines = top10['航空公司'].tolist()
        counts = top10['count'].tolist()
        bar = (
            Bar(init_opts=opts.InitOpts(bg_color='#001f3f'))
                .add_xaxis(airlines)
                .add_yaxis(
                series_name='航司',
                y_axis=counts,
                label_opts=opts.LabelOpts(position='right', color='#ffffff')
            )
                .reversal_axis()
                .set_global_opts(
                title_opts=opts.TitleOpts(
                    title=f"{date} 航班量 Top10 航司",
                    pos_left='center', title_textstyle_opts=opts.TextStyleOpts(color='#ffffff', font_size=20)
                ),
                tooltip_opts=opts.TooltipOpts(
                    trigger="item",
                    background_color="rgba(50,50,50,0.6)",  # 半透明度提高
                    border_width=0,
                    padding=3,  # 内边距减小
                    textstyle_opts=opts.TextStyleOpts(
                        color="#fff",
                        font_size=15  # 字体变小
                    ),
                    extra_css_text=(
                        "max-width:150px;"  # 限制最大宽度
                        "max-height:70px;"  # 限制最大高度
                        "overflow-y:auto;"  # 超出时显示垂直滚动
                        "box-shadow: 0 1px 4px rgba(0,0,0,0.25);"  # 投影更轻
                        "border-radius:4px;"  # 微圆角
                    )
                ),
                xaxis_opts=opts.AxisOpts(
                    name='航班量', axislabel_opts=opts.LabelOpts(color='#ffffff'),
                    splitline_opts=opts.SplitLineOpts(is_show=False)
                ),
                yaxis_opts=opts.AxisOpts(name='航司', axislabel_opts=opts.LabelOpts(color='#ffffff', interval=0, ),
                                         splitline_opts=opts.SplitLineOpts(is_show=False)
                                         ),
                legend_opts=opts.LegendOpts(is_show=False)
            )
        )
        tl.add(bar, str(date))
    return tl

# 3.航班班次排名前15分布柱状图
def airline_flight_counts():
    airline_counts = df["航空公司"].value_counts().head(15)

    bar = (
        Bar(init_opts=opts.InitOpts(
            theme=ThemeType.DARK
            ))
            .add_xaxis(airline_counts.sort_values(ascending=True).index.tolist())
            .add_yaxis(
            series_name="航班数量",
            y_axis=airline_counts.sort_values(ascending=True).values.tolist(),
            itemstyle_opts=opts.ItemStyleOpts(
                color=JsCode('''function(params) {
                    return ['#5470c6','#91cc75','#fac858','#ee6666','#73c0de',
                            '#3ba272','#fc8452','#9a60b4','#ea7ccc'][params.dataIndex % 9]
                }''')
            ),
            label_opts=opts.LabelOpts(
                position="right",
                color="#fff",
                font_size=14,
                font_weight="bold",
                formatter="{c} 班次",
                distance=20,
                background_color="rgba(30,30,30,0.7)",
                border_width=1,
                border_color="#666")
        )
            .reversal_axis()
            .set_global_opts(
            title_opts=opts.TitleOpts(
                title="航空公司航班班次分布", pos_left='center',
                title_textstyle_opts=opts.TextStyleOpts(
                    color="#fff",
                    font_size=24,
                    font_weight="bold")),
            legend_opts=opts.LegendOpts(
                orient="vertical",
                pos_left="right",
                pos_top='top',
                item_width=40,
                item_height=20
            ),
            xaxis_opts=opts.AxisOpts(
                name="航班班次",
                axislabel_opts=opts.LabelOpts(color="#ccc")),
            yaxis_opts=opts.AxisOpts(
                axislabel_opts=opts.LabelOpts(
                    color="#ccc",
                    font_size=12,
                    interval=0,
                    rotate=45)),
            tooltip_opts=opts.TooltipOpts(
                trigger="item",
                background_color="rgba(50,50,50,0.6)",  # 半透明度提高
                border_width=0,
                padding=3,  # 内边距减小
                textstyle_opts=opts.TextStyleOpts(
                    color="#fff",
                    font_size=15  # 字体变小
                ),
                extra_css_text=(
                    "max-width:150px;"  # 限制最大宽度
                    "max-height:70px;"  # 限制最大高度
                    "overflow-y:auto;"  # 超出时显示垂直滚动
                    "box-shadow: 0 1px 4px rgba(0,0,0,0.25);"  # 投影更轻
                    "border-radius:4px;"  # 微圆角
                )
            ),
            datazoom_opts=opts.DataZoomOpts(
                type_="inside",
                range_start=0,
                range_end=100),
        )
    )
    return bar

# 4.航班出发时间分布图
def flight_departure_time_distribution():
    hour_counts = df["起飞小时"].value_counts().sort_index()

    bar = (
        Bar(init_opts=opts.InitOpts(
            theme=ThemeType.DARK,
            bg_color="rgba(0,0,0,0.8)"))
            .add_xaxis([f"{int(h):02d}:00" for h in hour_counts.index])
            .add_yaxis(
            series_name="航班数量",
            y_axis=hour_counts.values.tolist(),
            itemstyle_opts=opts.ItemStyleOpts(
                color=JsCode('''function(params) {
                    return new echarts.graphic.LinearGradient(0, 0, 0, 1, [{
                        offset: 0,
                        color: '#7CFC00'
                    }, {
                        offset: 1,
                        color: '#00BFFF'
                    }]);
                }''')
            ),
            label_opts=opts.LabelOpts(
                position="top",
                color="#fff",
                font_size=12,
                formatter="{c}")
        )
            .set_global_opts(
            title_opts=opts.TitleOpts(
                title="航班出发时间分布", pos_left='center',
                title_textstyle_opts=opts.TextStyleOpts(
                    color="#fff",
                    font_size=24,
                    font_weight="bold")),
            legend_opts=opts.LegendOpts(
                orient="vertical",
                pos_left="right",
                pos_top='top',
                item_width=40,
                item_height=20
            ),
            xaxis_opts=opts.AxisOpts(
                name="起飞时间",
                axislabel_opts=opts.LabelOpts(
                    color="#ccc",
                    rotate=45)),
            yaxis_opts=opts.AxisOpts(
                name="航班数量",
                axislabel_opts=opts.LabelOpts(color="#ccc")),
            tooltip_opts=opts.TooltipOpts(
                trigger="item",
                axis_pointer_type="shadow",
                background_color="rgba(50,50,50,0.6)",  # 半透明度提高
                border_width=0,
                padding=3,  # 内边距减小
                textstyle_opts=opts.TextStyleOpts(
                    color="#fff",
                    font_size=15  # 字体变小
                ),
                extra_css_text=(
                    "max-width:150px;"  # 限制最大宽度
                    "max-height:70px;"  # 限制最大高度
                    "overflow-y:auto;"  # 超出时显示垂直滚动
                    "box-shadow: 0 1px 4px rgba(0,0,0,0.25);"  # 投影更轻
                    "border-radius:4px;"  # 微圆角
                )
            ),
            datazoom_opts=opts.DataZoomOpts(
                type_="inside",
                range_start=0,
                range_end=100),
            visualmap_opts=opts.VisualMapOpts(
                min_=hour_counts.min(),
                max_=hour_counts.max(),
                dimension=1,
                is_piecewise=False,
                range_color=["#7CFC00", "#00BFFF"])
        )
    )
    bar.set_series_opts(
        markpoint_opts=opts.MarkPointOpts(
            data=[
                opts.MarkPointItem(type_="max", name="峰值"),
                opts.MarkPointItem(type_="min", name="低谷")
            ]
        ),
        markline_opts=opts.MarkLineOpts(
            data=[opts.MarkLineItem(type_="average", name="平均值")]
        )
    )
    return bar

# 5.top10繁忙城市对航线柱状图
def busiest_routes_bar(df):
    # 统计城市对频次
    city_pairs = df.apply(lambda row: (row['出发城市'], row['到达城市']), axis=1)
    pair_counts = Counter(city_pairs)

    # Top 10 最繁忙航线
    top_pairs = pair_counts.most_common(10)

    # 准备数据
    x_data = [f"{dep}-{arr}" for dep, arr in dict(top_pairs).keys()]
    y_data = list(dict(top_pairs).values())

    # 创建柱状图
    bar = (
        Bar(init_opts=opts.InitOpts(
            theme=ThemeType.DARK))
        .add_xaxis(x_data)
        .add_yaxis("航班数量", y_data,
                   itemstyle_opts=opts.ItemStyleOpts(
                       color=JsCode('''function(params) {
                           return new echarts.graphic.LinearGradient(0, 0, 0, 1, [{
                               offset: 0,
                               color: '#7CFC00'
                           }, {
                               offset: 1,
                               color: '#00BFFF'
                           }]);
                       }''')  # 渐变配色
                   ),
                   label_opts=opts.LabelOpts(
                       is_show=True,
                       color="#fff",
                       font_size=10))
        .set_global_opts(
            title_opts=opts.TitleOpts(title="Top 10 最繁忙航线（城市对）", pos_left='center',
                                      title_textstyle_opts=opts.TextStyleOpts(
                                          color="#fff",
                                          font_size=24
                                      )),
            legend_opts=opts.LegendOpts(
                orient="vertical",
                pos_left="right",
                pos_top='top',
                item_width=40,
                item_height=20
            ),
            xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=30)),
            yaxis_opts=opts.AxisOpts(name="航班数量"),
            tooltip_opts=opts.TooltipOpts(
                trigger="item",
                background_color="rgba(50,50,50,0.6)",  # 半透明度提高
                border_width=0,
                padding=3,  # 内边距减小
                textstyle_opts=opts.TextStyleOpts(
                    color="#fff",
                    font_size=15  # 字体变小
                ),
                extra_css_text=(
                    "max-width:150px;"  # 限制最大宽度
                    "max-height:70px;"  # 限制最大高度
                    "overflow-y:auto;"  # 超出时显示垂直滚动
                    "box-shadow: 0 1px 4px rgba(0,0,0,0.25);"  # 投影更轻
                    "border-radius:4px;"  # 微圆角
                )
            )
        )
        .set_series_opts(label_opts=opts.LabelOpts(position="top"))
    )
    return bar

# 6.前十大机型饼图
def aircraft_model_pie():
    df["机型"] = df["机型"].str.replace(r"波音737-\d+", "波音737系列", regex=True)
    model_counts = df["机型"].value_counts().head(10).reset_index()
    model_counts.columns = ["机型", "数量"]
    threshold = 2
    main_models = model_counts[model_counts["数量"] >= threshold]
    other_count = model_counts[model_counts["数量"] < threshold]["数量"].sum()
    data_pair = list(zip(main_models["机型"], main_models["数量"]))
    if other_count > 0:
        data_pair.append(("其他机型", other_count))

    pie = (
        Pie(init_opts=opts.InitOpts(theme=ThemeType.DARK))
            .add(
            series_name="机型分布",
            data_pair=data_pair,
            radius=["35%", "65%"],
            center=["50%", "50%"]
        )
            .set_global_opts(
            title_opts=opts.TitleOpts(title="机型使用占比", pos_left='center'),
            tooltip_opts=opts.TooltipOpts(
                trigger="item",
                background_color="rgba(50,50,50,0.6)",  # 半透明度提高
                border_width=0,
                padding=3,  # 内边距减小
                textstyle_opts=opts.TextStyleOpts(
                    color="#fff",
                    font_size=15  # 字体变小
                ),
                extra_css_text=(
                    "max-width:150px;"  # 限制最大宽度
                    "max-height:70px;"  # 限制最大高度
                    "overflow-y:auto;"  # 超出时显示垂直滚动
                    "box-shadow: 0 1px 4px rgba(0,0,0,0.25);"  # 投影更轻
                    "border-radius:4px;"  # 微圆角
                )
            ),
            legend_opts=opts.LegendOpts(
                orient="vertical",
                pos_left="right",
                pos_top='center',
                item_width=40,
                item_height=20
            )
        )
            .set_series_opts(
            tooltip_opts=opts.TooltipOpts(
                formatter="{b}: {c} 班次 ({d}%)"
            ),
            label_opts=opts.LabelOpts(
                formatter="{b|{b}}\n{d|{d}%}",
                rich={
                    "b": {"fontSize": 14, "color": "#fff"},
                    "d": {"fontSize": 12, "color": "#aaa"}
                }
            )
        )
    )
    return pie

# 7.航班量趋势折线图
def flight_trend_line():
    df['起飞日期'] = pd.to_datetime(df['起飞时间']).dt.date
    daily = df.groupby('起飞日期').size().reset_index(name='count')

    line = (
        Line(init_opts=opts.InitOpts())
            .add_xaxis(daily['起飞日期'].astype(str).tolist())
            .add_yaxis(
            series_name='航班量',
            y_axis=daily['count'].tolist(),
            is_smooth=True,
            label_opts=opts.LabelOpts(is_show=False)
        )
            .set_global_opts(
            title_opts=opts.TitleOpts(
                title='航班量趋势图',
                pos_top='5%',
                pos_left='center',
                title_textstyle_opts=opts.TextStyleOpts(color='#fff', font_size=18)
            ),
            tooltip_opts=opts.TooltipOpts(
                trigger="item",
                background_color="rgba(50,50,50,0.6)",  # 半透明度提高
                border_width=0,
                padding=3,  # 内边距减小
                textstyle_opts=opts.TextStyleOpts(
                    color="#fff",
                    font_size=15  # 字体变小
                ),
                extra_css_text=(
                    "max-width:150px;"  # 限制最大宽度
                    "max-height:70px;"  # 限制最大高度
                    "overflow-y:auto;"  # 超出时显示垂直滚动
                    "box-shadow: 0 1px 4px rgba(0,0,0,0.25);"  # 投影更轻
                    "border-radius:4px;"  # 微圆角
                )
            ),
            legend_opts=opts.LegendOpts(is_show=False),
            datazoom_opts=[
                opts.DataZoomOpts(type_='slider', pos_bottom='10%', range_start=0, range_end=100)
            ],
            xaxis_opts=opts.AxisOpts(
                type_='category',
                name='日期',
                axislabel_opts=opts.LabelOpts(rotate=45, color='#fff'),
                axisline_opts=opts.AxisLineOpts(
                    linestyle_opts=opts.LineStyleOpts(color='#aaa')
                ),
                splitline_opts=opts.SplitLineOpts(is_show=True)
            ),
            yaxis_opts=opts.AxisOpts(
                type_='value',
                name='航班量',
                min_=650,
                max_=900,
                axislabel_opts=opts.LabelOpts(color='#fff', formatter="{value}"),
                splitline_opts=opts.SplitLineOpts(is_show=True)
            )
        )
            .set_dark_mode()
    )
    return line

# 8.航空公司市场份额饼图
def airline_share_pie():
    counts = df['航空公司'].value_counts().reset_index()
    counts.columns = ['airline', 'count']
    data_pair = [list(z) for z in zip(counts['airline'], counts['count'])]
    pie = (
        Pie(init_opts=opts.InitOpts(theme=ThemeType.DARK))
            .add('', data_pair, radius=['30%', '60%'])
            .set_global_opts(
            title_opts=opts.TitleOpts(title='航空公司市场份额', pos_left='left'),
            tooltip_opts=opts.TooltipOpts(
                trigger="item",
                background_color="rgba(50,50,50,0.6)",  # 半透明度提高
                border_width=0,
                padding=3,  # 内边距减小
                textstyle_opts=opts.TextStyleOpts(
                    color="#fff",
                    font_size=15  # 字体变小
                ),
                extra_css_text=(
                    "max-width:150px;"  # 限制最大宽度
                    "max-height:70px;"  # 限制最大高度
                    "overflow-y:auto;"  # 超出时显示垂直滚动
                    "box-shadow: 0 1px 4px rgba(0,0,0,0.25);"  # 投影更轻
                    "border-radius:4px;"  # 微圆角
                )
            ),
            legend_opts=opts.LegendOpts(type_='scroll', pos_left='right', orient='vertical')
        )
            .set_series_opts(label_opts=opts.LabelOpts(formatter='{b}: {d}%'))
    )
    return pie

# 9.准点率仪表盘
def ontime_rate_gauge():
    df['delay_min'] = (
        df['平均误点时间']
            .astype(str)
            .str.extract(r'(\d+)')
            .iloc[:, 0]
            .astype(float)
            .fillna(0)
    )
    ontime_count = df[df['delay_min'] <= 15].shape[0]
    total = df.shape[0]
    rate = round(ontime_count / total * 100, 1)
    gauge = (
        Gauge(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='600px', height='400px'))
        .add(
            series_name="准点率",
            data_pair=[("准点率", rate)],
            center=["50%", "60%"],
            radius="80%",
            start_angle=210,
            end_angle=-30,
            axisline_opts=opts.AxisLineOpts(
                linestyle_opts=opts.LineStyleOpts(
                    width=30,
                    color=[
                        [0.6, "#FF6E76"],   # 红色段：60%以下
                        [0.8, "#FDDD60"],   # 黄色段：60%~80%
                        [1.0, "#58D9F9"]    # 蓝色段：80%~100%
                    ]
                )
            ),
            detail_label_opts=opts.GaugeDetailOpts(
                formatter="{value}%",
                font_size=28,
                color="#FFFFFF",
                font_weight="bold",
                offset_center=[0, "60%"]
            ),
            title_label_opts=opts.GaugeTitleOpts(
                font_size=18,           # 放大字号
                color="#FFFFFF",        # 白色
                offset_center=[0, "80%"]  # 向下偏移到底部三分之一
            )
        )
        .set_global_opts(
            title_opts=opts.TitleOpts(title="整体准点率", pos_left="center", pos_top="10%"),
            legend_opts=opts.LegendOpts(is_show=False),
            tooltip_opts=opts.TooltipOpts(
                trigger="item",
                background_color="rgba(50,50,50,0.6)",  # 半透明度提高
                border_width=0,
                padding=3,  # 内边距减小
                textstyle_opts=opts.TextStyleOpts(
                    color="#fff",
                    font_size=15  # 字体变小
                ),
                extra_css_text=(
                    "max-width:150px;"  # 限制最大宽度
                    "max-height:70px;"  # 限制最大高度
                    "overflow-y:auto;"  # 超出时显示垂直滚动
                    "box-shadow: 0 1px 4px rgba(0,0,0,0.25);"  # 投影更轻
                    "border-radius:4px;"  # 微圆角
                )
            )
            )
        )
    return gauge

# 10.前六航空公司每月航班数量对比（堆叠图）
def top6_airlines_monthly_flights(df):
    df['起飞时间'] = pd.to_datetime(df['起飞时间'])
    df['月份'] = df['起飞时间'].dt.month

    # 获取前六大航空公司
    top_airlines = df['航空公司'].value_counts().nlargest(6).index.tolist()
    top_df = df[df['航空公司'].isin(top_airlines)]

    # 按月份和航空公司统计
    monthly_counts = top_df.groupby(['月份', '航空公司']).size().unstack(fill_value=0)
    monthly_counts = monthly_counts.sort_index()

    # 创建堆叠柱状图
    bar = (
        Bar(init_opts=opts.InitOpts(
            theme=ThemeType.DARK))
        .add_xaxis([f"{month}月" for month in monthly_counts.index])
    )
    for airline in monthly_counts.columns:
        bar.add_yaxis(airline, monthly_counts[airline].tolist(), stack="stack1")

    bar.set_global_opts(
        title_opts=opts.TitleOpts(title="前六航空公司每月航班数量对比（堆叠图）", pos_left='center',
                                  title_textstyle_opts=opts.TextStyleOpts(
                                      color="#fff",
                                      font_size=24)),
        tooltip_opts=opts.TooltipOpts(
            trigger="item",
            background_color="rgba(50,50,50,0.6)",  # 半透明度提高
            border_width=0,
            padding=3,  # 内边距减小
            textstyle_opts=opts.TextStyleOpts(
                color="#fff",
                font_size=15  # 字体变小
            ),
            extra_css_text=(
                "max-width:150px;"  # 限制最大宽度
                "max-height:70px;"  # 限制最大高度
                "overflow-y:auto;"  # 超出时显示垂直滚动
                "box-shadow: 0 1px 4px rgba(0,0,0,0.25);"  # 投影更轻
                "border-radius:4px;"  # 微圆角
            )
        ),
        xaxis_opts=opts.AxisOpts(name="月份"),
        yaxis_opts=opts.AxisOpts(name="航班数量"),
        legend_opts=opts.LegendOpts(pos_bottom="5%")
    )
    return bar

# 11.票价分布图
def price_distribution_chart(df):
    # 提取票价数据，剔除空值
    prices = df['价格(元)'].dropna()
    # 设置价格区间
    bins = list(range(0, 3000, 500))
    labels = [f"{i}-{i + 500}" for i in bins[:-1]]
    # 统计落在每个区间的票价数量
    price_counts, _ = np.histogram(prices, bins=bins)
    # 创建柱状图
    bar = (
        Bar(init_opts=opts.InitOpts(
            theme=ThemeType.DARK))
        .add_xaxis(labels)
        .add_yaxis("航班数量", price_counts.tolist(),
                   label_opts=opts.LabelOpts(
                       is_show=True,
                       color="#fff",
                       font_size=10))
        .set_global_opts(
            title_opts=opts.TitleOpts(title="票价分布图（单位：元）", pos_left='center',
                                      title_textstyle_opts=opts.TextStyleOpts(
                                          color="#fff",
                                          font_size=24
                                      )),
            tooltip_opts=opts.TooltipOpts(
                trigger="item",
                background_color="rgba(50,50,50,0.6)",  # 半透明度提高
                border_width=0,
                padding=3,  # 内边距减小
                textstyle_opts=opts.TextStyleOpts(
                    color="#fff",
                    font_size=15  # 字体变小
                ),
                extra_css_text=(
                    "max-width:150px;"  # 限制最大宽度
                    "max-height:70px;"  # 限制最大高度
                    "overflow-y:auto;"  # 超出时显示垂直滚动
                    "box-shadow: 0 1px 4px rgba(0,0,0,0.25);"  # 投影更轻
                    "border-radius:4px;"  # 微圆角
                )
            ),
        legend_opts=opts.LegendOpts(
                orient="vertical",
                pos_left="right",
                pos_top='top',
                item_width=40,
                item_height=20
            ),
            xaxis_opts=opts.AxisOpts(name="票价区间"),
            yaxis_opts=opts.AxisOpts(name="航班数量"),
        )
        .set_series_opts(label_opts=opts.LabelOpts(position="top"))
    )
    return bar

# 12.按飞行时长分组的票价分布箱线图
def flight_duration_price_boxplot(df):
    df['收入'] = df['价格(元)'] * df['人数']
    flight_duration_bins = pd.cut(df['飞行时长(分钟)'], bins=5)
    groups_price = []
    for group_name, group_data in df.groupby(flight_duration_bins):
        groups_price.append(group_data['价格(元)'].tolist())

    boxplot_price = Boxplot(init_opts=opts.InitOpts(theme='dark'))
    boxplot_price_data = boxplot_price.prepare_data(groups_price)
    boxplot_price.add_xaxis([str(interval) for interval in df['飞行时长(分钟)'].groupby(flight_duration_bins).groups.keys()])
    boxplot_price.add_yaxis("票价", boxplot_price.prepare_data(groups_price), itemstyle_opts=opts.ItemStyleOpts(border_color="#2ecc71"),
                            label_opts=opts.LabelOpts(is_show=True, position="top"))
    boxplot_price.set_global_opts(
        title_opts=opts.TitleOpts(title="不同飞行时长区间的票价分布箱线图", pos_left="center",
                                  title_textstyle_opts=opts.TextStyleOpts(
                                      font_size=24,
                                      font_weight="bold")),
        tooltip_opts=opts.TooltipOpts(
            trigger="item",
            background_color="rgba(50,50,50,0.6)",  # 半透明度提高
            border_width=0,
            padding=3,  # 内边距减小
            textstyle_opts=opts.TextStyleOpts(
                color="#fff",
                font_size=15  # 字体变小
            ),
            extra_css_text=(
                "max-width:150px;"  # 限制最大宽度
                "max-height:70px;"  # 限制最大高度
                "overflow-y:auto;"  # 超出时显示垂直滚动
                "box-shadow: 0 1px 4px rgba(0,0,0,0.25);"  # 投影更轻
                "border-radius:4px;"  # 微圆角
            )
        ),
        legend_opts=opts.LegendOpts(
            orient="vertical",
            pos_left="right",
            pos_top='top',
            item_width=40,
            item_height=20
        ),
        xaxis_opts=opts.AxisOpts(name="飞行时长区间"),
        yaxis_opts=opts.AxisOpts(name="票价"),
    )
    return boxplot_price

# 13.按飞行时长分组计算平均收入的折线图
def avg_income_by_flight_duration(df):
    df['收入'] = df['价格(元)'] * df['人数']
    flight_duration_bins = pd.cut(df['飞行时长(分钟)'], bins=10)
    avg_income_by_duration = df.groupby(flight_duration_bins)['收入'].mean()
    line = (
        Line(init_opts=opts.InitOpts(theme='dark'))
        .add_xaxis([str(interval) for interval in avg_income_by_duration.index])
        .add_yaxis("平均收入", avg_income_by_duration.values.tolist(), is_smooth=True)
        .set_global_opts(
            title_opts=opts.TitleOpts(title="平均收入随飞行时长变化", pos_left="center",
                                      title_textstyle_opts=opts.TextStyleOpts(
                                          font_size=24,
                                          font_weight="bold")),
            tooltip_opts=opts.TooltipOpts(
                trigger="item",
                background_color="rgba(50,50,50,0.6)",  # 半透明度提高
                border_width=0,
                padding=3,  # 内边距减小
                textstyle_opts=opts.TextStyleOpts(
                    color="#fff",
                    font_size=15  # 字体变小
                ),
                extra_css_text=(
                    "max-width:150px;"  # 限制最大宽度
                    "max-height:70px;"  # 限制最大高度
                    "overflow-y:auto;"  # 超出时显示垂直滚动
                    "box-shadow: 0 1px 4px rgba(0,0,0,0.25);"  # 投影更轻
                    "border-radius:4px;"  # 微圆角
                )
            ),
            legend_opts=opts.LegendOpts(
                orient="vertical",
                pos_left="right",
                pos_top='top',
                item_width=40,
                item_height=20
            ),
        xaxis_opts=opts.AxisOpts(name="飞行时长区间"),
        yaxis_opts=opts.AxisOpts(name="平均收入"),
        )
    )
    return line

# 14.top10繁忙城市客流量热力图
def generate_route_heatmap(df, top_n=10):
    # 聚合客流量
    route_flow = (
        df.groupby(["出发城市", "到达城市"], as_index=False)
        .agg({"人数": "sum"})
        .rename(columns={"人数": "总客流"})
    )

    # 选取最繁忙的城市
    dep_flow = route_flow.groupby("出发城市")["总客流"].sum().nlargest(10)
    arr_flow = route_flow.groupby("到达城市")["总客流"].sum().nlargest(10)
    top_deps = dep_flow.index.tolist()
    top_arrs = arr_flow.index.tolist()

    filtered = route_flow[
        route_flow["出发城市"].isin(top_deps) &
        route_flow["到达城市"].isin(top_arrs)
        ]

    # 准备 HeatMap 数据格式
    data = [
        [top_deps.index(dep), top_arrs.index(arr), val]
        for dep, arr, val in zip(
            filtered["出发城市"],
            filtered["到达城市"],
            filtered["总客流"]
        )
    ]

    # 绘制热力图（暗色背景 + 定制布局）
    heatmap = (
        HeatMap(
            init_opts=opts.InitOpts(
                theme=ThemeType.DARK  # 暗色主题
            )
        )
        .add_xaxis(top_arrs)
        .add_yaxis(
            series_name="客流量（人次）",
            yaxis_data=top_deps,
            value=data,
            label_opts=opts.LabelOpts(
                is_show=True,
                position="inside"
            )
        )
        .set_global_opts(
            title_opts=opts.TitleOpts(
                title="2024年10-12月 繁忙城市航线客流热力图",
                pos_left="center",  # 标题居中
                pos_top="5%",  # 距离上方 5%
                title_textstyle_opts=opts.TextStyleOpts(font_size=20)
            ),
            legend_opts=opts.LegendOpts(
                pos_top="5%",  # 图例放在右上
                pos_right="5%",
                textstyle_opts=opts.TextStyleOpts(color="#ffffff")
            ),
            tooltip_opts=opts.TooltipOpts(
                trigger="item",
                background_color="rgba(50,50,50,0.6)",  # 半透明度提高
                border_width=0,
                padding=3,  # 内边距减小
                textstyle_opts=opts.TextStyleOpts(
                    color="#fff",
                    font_size=15  # 字体变小
                ),
                extra_css_text=(
                    "max-width:150px;"  # 限制最大宽度
                    "max-height:70px;"  # 限制最大高度
                    "overflow-y:auto;"  # 超出时显示垂直滚动
                    "box-shadow: 0 1px 4px rgba(0,0,0,0.25);"  # 投影更轻
                    "border-radius:4px;"  # 微圆角
                )
            ),
            visualmap_opts=opts.VisualMapOpts(
                min_=filtered["总客流"].min(),
                max_=filtered["总客流"].max(),
                orient="vertical",
                is_calculable=True,
                range_text=["高", "低"],
                pos_right="1%",  # 色卡放左下
                pos_bottom="5%",
                textstyle_opts=opts.TextStyleOpts(color="#ffffff")
            ),
            xaxis_opts=opts.AxisOpts(
                axislabel_opts=opts.LabelOpts(
                    rotate=45,
                    color="#ffffff"
                )
            ),
            yaxis_opts=opts.AxisOpts(
                axislabel_opts=opts.LabelOpts(
                    font_size=12,
                    color="#ffffff"
                )
            )
        )
    )
    return heatmap


# ===添加以下函数和路由===
from flask import Flask, render_template, request, redirect, url_for, session
from werkzeug.security import generate_password_hash, check_password_hash
import sqlite3
import os

app.secret_key = 'your_secret_key'
# 生成一个随机秘钥，这样每次重启，之前签名过的cookie都会失效——主要用于测试，别在生产环境这样用，否则所有用户都得重新登录。
app.secret_key = os.urandom(24)

# 静态管理员账号密码
ADMIN_USERNAME = 'admin'
ADMIN_PASSWORD = 'admin123'

# 初始化数据库
def init_db():
    conn = sqlite3.connect('users.db')
    c = conn.cursor()
    c.execute('''CREATE TABLE IF NOT EXISTS users
                 (id INTEGER PRIMARY KEY AUTOINCREMENT,
                 username TEXT NOT NULL UNIQUE,
                 password TEXT NOT NULL,
                 email TEXT,
                 role TEXT NOT NULL)''')

    # 检查 email 列是否存在
    c.execute("PRAGMA table_info(users)")
    columns = [col[1] for col in c.fetchall()]
    if 'email' not in columns:
        # 添加 email 列
        c.execute("ALTER TABLE users ADD COLUMN email TEXT")

    # 添加密码重置令牌表
    c.execute('''CREATE TABLE IF NOT EXISTS password_reset_tokens
                 (id INTEGER PRIMARY KEY AUTOINCREMENT,
                 user_id INTEGER NOT NULL,
                 token TEXT NOT NULL UNIQUE,
                 expiration TIMESTAMP NOT NULL,
                 FOREIGN KEY (user_id) REFERENCES users(id))''')

    # 插入静态管理员账号
    admin_password_hash = generate_password_hash(ADMIN_PASSWORD)
    try:
        c.execute("INSERT INTO users (username, password, role, email) VALUES (?, ?, ?, ?)",
                  (ADMIN_USERNAME, admin_password_hash, 'admin', 'admin@example.com'))
        conn.commit()
    except sqlite3.IntegrityError:
        pass
    conn.close()

# 添加邮件发送函数
import smtplib
from email.mime.text import MIMEText
from email.header import Header
from datetime import datetime, timedelta
import secrets

# 邮件配置（在实际应用中，应使用环境变量存储这些敏感信息）
SMTP_SERVER = "smtp.qq.com"  # QQ邮箱SMTP服务器
SMTP_PORT = 465  # QQ邮箱SSL端口
SMTP_USER = "1416078070@qq.com"  # 发送邮箱地址
SMTP_PASSWORD = "xykdfvzxbxoxiagj"  # QQ邮箱SMTP授权码
SENDER_EMAIL = "1416078070@qq.com"  # 发送邮箱地址

def send_password_reset_email(email, reset_link):
    """发送密码重置邮件"""
    subject = "航班数据分析系统 - 密码重置请求"
    body = f"""
    <html>
    <body>
        <h2>航班数据分析系统密码重置</h2>
        <p>我们收到了您重置密码的请求。请点击下面的链接重置您的密码：</p>
        <p><a href="{reset_link}">{reset_link}</a></p>
        <p>如果您没有请求重置密码，请忽略此邮件。</p>
        <p>此链接将在1小时后失效。</p>
        <br>
        <p>谢谢，</p>
        <p>航班数据分析系统团队</p>
    </body>
    </html>
    """

    msg = MIMEText(body, 'html', 'utf-8')
    msg['Subject'] = Header(subject, 'utf-8')
    msg['From'] = SENDER_EMAIL
    msg['To'] = email

    try:
        # 使用SSL连接
        server = smtplib.SMTP_SSL(SMTP_SERVER, SMTP_PORT)
        server.login(SMTP_USER, SMTP_PASSWORD)
        server.send_message(msg)
        server.quit()
        return True
    except Exception as e:
        print(f"发送邮件失败: {e}")
        return False


# 获取所有用户信息
def get_all_users():
    conn = sqlite3.connect('users.db')
    c = conn.cursor()
    c.execute('SELECT id, username, password, email, role FROM users')
    users = c.fetchall()
    conn.close()
    return users

# 添加用户
def add_user(username, password, role, email=''):
    hashed_password = generate_password_hash(password)
    try:
        conn = sqlite3.connect('users.db')
        c = conn.cursor()
        c.execute('INSERT INTO users (username, password, role, email) VALUES (?,?,?,?)',
                 (username, hashed_password, role, email))
        conn.commit()
        conn.close()
        return True
    except sqlite3.IntegrityError:
        return False

# 删除用户
def delete_user(user_id):
    conn = sqlite3.connect('users.db')
    c = conn.cursor()
    c.execute('DELETE FROM users WHERE id =?', (user_id,))
    conn.commit()
    conn.close()

# 更新用户信息
def update_user(user_id, username, role, password=None):
    conn = sqlite3.connect('users.db')
    c = conn.cursor()

    if password and password.strip() != "":
        hashed_password = generate_password_hash(password)
        c.execute('UPDATE users SET username=?, password=?, role=? WHERE id=?',
                  (username, hashed_password, role, user_id))
    else:
        c.execute('UPDATE users SET username=?, role=? WHERE id=?',
                  (username, role, user_id))

    conn.commit()
    conn.close()

# 新增用户路由
@app.route('/add_user', methods=['POST'])
def add_user_route():
    username = request.form.get('username')
    password = request.form.get('password')
    role = request.form.get('role')

    if add_user(username, password, role):
        return redirect(url_for('user_manage', action='add'))
    else:
        return "用户名已存在"

# 删除用户路由
@app.route('/delete_user/<int:user_id>', methods=['GET'])
def delete_user_route(user_id):
    if 'role' not in session or session['role'] != 'admin':
        return redirect(url_for('login'))

    # 防止删除管理员自己
    if user_id == 1:  # 管理员ID为1
        return redirect(url_for('user_manage', error='不能删除管理员账户'))

    delete_user(user_id)
    return redirect(url_for('user_manage', action='delete'))

# 更新用户路由
@app.route('/update_user', methods=['POST'])
def update_user_route():
    user_id = request.form.get('user_id')
    username = request.form.get('username')
    role = request.form.get('role')
    password = request.form.get('password')  # 可能为空

    update_user(user_id, username, role, password)

    # 添加重定向到用户管理页面并显示更新成功消息
    return redirect(url_for('user_manage', action='update', updated=True))


@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        # 获取表单数据
        username = request.form.get('username')
        password = request.form.get('password')

        print(f"登录请求 - 用户名: {username}")

        try:
            # 连接数据库
            conn = sqlite3.connect('users.db')
            c = conn.cursor()

            # 仅根据用户名查询用户
            c.execute("SELECT * FROM users WHERE username = ?", (username,))
            user = c.fetchone()
            conn.close()

            print(f"查询结果: {user}")

            if user:
                # 验证密码
                password_valid = check_password_hash(user[2], password)
                print(f"密码验证结果: {password_valid}")

                if password_valid:
                    # 设置会话 - 使用数据库中的实际角色
                    session['username'] = username
                    session['role'] = user[4]  # 第5列是角色
                    print(f"会话已设置: username={username}, role={user[4]}")

                    # 根据实际角色重定向
                    if user[4] == 'admin':
                        print("重定向到管理员首页")
                        return redirect(url_for('admin_index'))
                    else:
                        print("重定向到普通用户首页")
                        return redirect(url_for('index'))
                else:
                    print("密码验证失败")
                    return render_template('login_two.html', error='密码错误')
            else:
                print("用户不存在")
                return render_template('login_two.html', error='用户名不存在')

        except Exception as e:
            print(f"登录错误: {e}")
            return render_template('login_two.html', error=f"登录失败: {str(e)}")

    # GET 请求，显示登录页面
    return render_template('login_two.html')

@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        username = request.form.get('registerUsername')
        password = request.form.get('registerPassword')
        confirm_password = request.form.get('registerConfirmPassword')
        email = request.form.get('registerEmail')  # 获取邮箱
        role = 'users'  # 固定为普通用户

        # 验证邮箱格式
        if not email or '@' not in email:
            return render_template('login_two.html', error='请输入有效的电子邮箱')

        # 验证密码长度
        if len(password) < 6:
            return render_template('login_two.html', error='密码长度至少为6个字符')

        # 验证密码一致性
        if password != confirm_password:
            return render_template('login_two.html', error='两次输入的密码不一致')

        try:
            conn = sqlite3.connect('users.db')
            c = conn.cursor()

            # 检查用户名是否已存在
            c.execute("SELECT * FROM users WHERE username = ?", (username,))
            if c.fetchone():
                conn.close()
                return render_template('login_two.html', error='用户名已存在')

            # 检查邮箱是否已注册
            c.execute("SELECT * FROM users WHERE email = ?", (email,))
            if c.fetchone():
                conn.close()
                return render_template('login_two.html', error='该邮箱已注册')

            # 插入用户信息（包含邮箱）
            c.execute("INSERT INTO users (username, password, role, email) VALUES (?, ?, ?, ?)",
                      (username, generate_password_hash(password), role, email))

            conn.commit()
            conn.close()
            return redirect(url_for('login'))
        except Exception as e:
            return render_template('login_two.html', error=f'注册失败: {str(e)}')

    # GET 请求显示注册表单
    return render_template('login_two.html')

@app.route('/forgot_password', methods=['POST'])
def forgot_password():
    email = request.form.get('email')
    if not email:
        return render_template('login_two.html', error='请输入邮箱地址')

    conn = sqlite3.connect('users.db')
    c = conn.cursor()
    c.execute("SELECT id FROM users WHERE email = ?", (email,))
    user = c.fetchone()

    if not user:
        return render_template('login_two.html', error='该邮箱未注册')

    # 生成重置令牌（安全随机字符串）
    token = secrets.token_urlsafe(32)
    expiration = datetime.now() + timedelta(hours=1)  # 1小时后过期

    # 保存令牌到数据库
    c.execute("INSERT INTO password_reset_tokens (user_id, token, expiration) VALUES (?, ?, ?)",
              (user[0], token, expiration))
    conn.commit()
    conn.close()

    # 创建重置链接
    reset_link = url_for('reset_password', token=token, _external=True)

    # 发送邮件
    if send_password_reset_email(email, reset_link):
        return render_template('login_two.html', message='密码重置链接已发送至您的邮箱，请查收')
    else:
        return render_template('login_two.html', error='发送重置邮件失败，请稍后再试')


@app.route('/reset_password/<token>', methods=['GET', 'POST'])
def reset_password(token):
    # 验证令牌
    conn = sqlite3.connect('users.db')
    c = conn.cursor()
    c.execute("""
        SELECT users.id, expiration 
        FROM password_reset_tokens 
        JOIN users ON password_reset_tokens.user_id = users.id 
        WHERE token = ? AND expiration > ?
    """, (token, datetime.now()))
    token_data = c.fetchone()

    if not token_data:
        return render_template('reset_password.html', error='无效或过期的重置链接')

    user_id = token_data[0]

    if request.method == 'POST':
        password = request.form.get('password')
        confirm_password = request.form.get('confirm_password')

        if not password or len(password) < 6:
            return render_template('reset_password.html', token=token, error='密码长度至少为6个字符')

        if password != confirm_password:
            return render_template('reset_password.html', token=token, error='两次输入的密码不一致')

        # 更新密码
        password_hash = generate_password_hash(password)
        c.execute("UPDATE users SET password = ? WHERE id = ?", (password_hash, user_id))

        # 删除使用过的令牌
        c.execute("DELETE FROM password_reset_tokens WHERE token = ?", (token,))
        conn.commit()
        conn.close()

        return render_template('reset_password.html', success='密码重置成功，请使用新密码登录')

    conn.close()
    return render_template('reset_password.html', token=token)

@app.route('/logout')
def logout():
    session.clear()  # 清除所有会话数据
    return redirect(url_for('login'))


# 图表生成功能
def generate_charts(df, city_coords):
    return {
        'flight_map_html': flight_dynamic_map(df, city_coords).render_embed(),
        'top10_airlines_html': daily_top10_timeline(df).render_embed(),
        'airline_counts_html': airline_flight_counts().render_embed(),
        'departure_time_html': flight_departure_time_distribution().render_embed(),
        'busiest_routes_html': busiest_routes_bar(df).render_embed(),
        'aircraft_model_html': aircraft_model_pie().render_embed(),
        'flight_trend_html': flight_trend_line().render_embed(),
        'airline_share_html': airline_share_pie().render_embed(),
        'ontime_rate_html': ontime_rate_gauge().render_embed(),
        'top6_flights_html': top6_airlines_monthly_flights(df).render_embed(),
        'price_distribution_html': price_distribution_chart(df).render_embed(),
        'boxplot_html': flight_duration_price_boxplot(df).render_embed(),
        'line_chart_html': avg_income_by_flight_duration(df).render_embed()
    }

@app.route('/admin_index')
def admin_index():
    if 'role' not in session or session['role'] != 'admin':
        return redirect(url_for('login'))

    # 获取用户数据
    users = get_all_users()

    # 生成图表数据
    try:
        charts = generate_charts(df, city_coords)
    except Exception as e:
        charts = {f'error_{k}': f'<div class="text-red-500">图表生成失败: {str(e)}</div>'
                  for k in [
                      'flight_map', 'top10_airlines', 'airline_counts',
                      'departure_time', 'busiest_routes', 'aircraft_model',
                      'flight_trend', 'airline_share', 'ontime_rate',
                      'top6_flights', 'price_distribution', 'boxplot', 'line_chart'
                  ]}

    # 合并用户数据和图表数据
    context = {
        'users': users,
        **charts
    }

    return render_template('admin_index.html', **context)

@app.route('/user_manage')
def user_manage():
    if 'role' not in session or session['role'] != 'admin':
        return redirect(url_for('index'))

    # 获取用户数据
    users = get_all_users()
    return render_template('user_manage.html', users=users)

@app.route('/')
def index():
    try:
        if 'username' not in session:  # 检查用户是否登录
            return redirect(url_for('login'))

        # 生成各图表
        flight_map = flight_dynamic_map(df, city_coords)
        flight_map_html = flight_map.render_embed()

        top10_airlines = daily_top10_timeline(df)
        top10_airlines_html = top10_airlines.render_embed()

        airline_counts = airline_flight_counts()
        airline_counts_html = airline_counts.render_embed()

        departure_time = flight_departure_time_distribution()
        departure_time_html = departure_time.render_embed()

        busiest_routes = busiest_routes_bar(df)
        busiest_routes_html = busiest_routes.render_embed()

        aircraft_model = aircraft_model_pie()
        aircraft_model_html = aircraft_model.render_embed()

        flight_trend = flight_trend_line()
        flight_trend_html = flight_trend.render_embed()

        airline_share = airline_share_pie()
        airline_share_html = airline_share.render_embed()

        ontime_rate = ontime_rate_gauge()
        ontime_rate_html = ontime_rate.render_embed()

        top6_flights = top6_airlines_monthly_flights(df)
        top6_flights_html = top6_flights.render_embed()

        price_distribution = price_distribution_chart(df)
        price_distribution_html = price_distribution.render_embed()

        boxplot = flight_duration_price_boxplot(df)
        boxplot_html = boxplot.render_embed()

        line_chart = avg_income_by_flight_duration(df)
        line_chart_html = line_chart.render_embed()

        return render_template('index.html',
                               flight_map_html=flight_map_html,
                               top10_airlines_html=top10_airlines_html,
                               airline_counts_html=airline_counts_html,
                               departure_time_html=departure_time_html,
                               busiest_routes_html=busiest_routes_html,
                               aircraft_model_html=aircraft_model_html,
                               flight_trend_html=flight_trend_html,
                               airline_share_html=airline_share_html,
                               ontime_rate_html=ontime_rate_html,
                               top6_flights_html=top6_flights_html,
                               price_distribution_html=price_distribution_html,
                               boxplot_html=boxplot_html,
                               line_chart_html=line_chart_html)
    except Exception as e:
        app.logger.error(f"Error in index route: {str(e)}")
        return render_template('error.html', error=str(e)), 500

# 航线分析的路由
@app.route('/routes')
def routes():
    flight_map = flight_dynamic_map(df, city_coords)
    flight_map_html = flight_map.render_embed()

    busiest_routes = busiest_routes_bar(df)
    busiest_routes_html = busiest_routes.render_embed()

    heatmap = generate_route_heatmap(df, 10)
    heatmap_html = heatmap.render_embed()

    return render_template('routes.html',
                           flight_map=flight_map_html,
                           busiest_routes=busiest_routes_html,
                           heatmap=heatmap_html)

# 航空公司的路由
@app.route('/airlines')
def airlines():
    # 生成航空公司相关图表
    airline_counts = airline_flight_counts()
    airline_counts_html = airline_counts.render_embed()

    airline_share = airline_share_pie()
    airline_share_html = airline_share.render_embed()

    aircraft_model = aircraft_model_pie()
    aircraft_model_html = aircraft_model.render_embed()

    top6_flights = top6_airlines_monthly_flights(df)
    top6_flights_html = top6_flights.render_embed()

    return render_template('airlines.html',
                           airline_counts=airline_counts_html,
                           airline_share=airline_share_html,
                           aircraft_model=aircraft_model_html,
                           top6_flights=top6_flights_html)

# 航班统计的路由
@app.route('/statistics')
def statistics():
    departure_time = flight_departure_time_distribution()
    departure_time_html = departure_time.render_embed()

    flight_trend = flight_trend_line()
    flight_trend_html = flight_trend.render_embed()

    top10_airlines = daily_top10_timeline(df)
    top10_airlines_html = top10_airlines.render_embed()

    ontime_rate = ontime_rate_gauge()
    ontime_rate_html = ontime_rate.render_embed()

    return render_template('statistics.html',
                           departure_time=departure_time_html,
                           flight_trend=flight_trend_html,
                           top10_airlines=top10_airlines_html,
                           ontime_rate=ontime_rate_html)

# 票价-收入的路由
@app.route('/price_revenue')
def price_revenue():
    # 生成票价分布图
    price_distribution = price_distribution_chart(df)
    price_distribution_html = price_distribution.render_embed()

    boxplot = flight_duration_price_boxplot(df)
    boxplot_html = boxplot.render_embed()

    line_chart = avg_income_by_flight_duration(df)
    line_chart_html = line_chart.render_embed()

    return render_template('price_revenue.html',
                           price_distribution=price_distribution_html,
                           boxplot=boxplot_html,
                           line_chart=line_chart_html)

# 关于系统的路由
@app.route('/about')
def about():
    return render_template('about.html')

# mysql连接
import pymysql
from pymysql import Error  # 使用pymysql的错误处理

# MySQL配置
MYSQL_CONFIG = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '123456',
    'database': 'airline_data',
    'charset': 'utf8mb4',  # 添加字符集配置
    'cursorclass': pymysql.cursors.DictCursor  # 确保返回字典格式结果
}

@app.route('/search', methods=['GET'])
def search_flights():
    query = request.args.get('query', '')
    if not query:
        return jsonify([])
    app.logger.info(f"收到搜索请求: {query}")

    connection = None  # 初始化连接对象
    cursor = None  # 初始化游标对象

    try:
        # 调试：打印数据库配置
        app.logger.info(f"数据库配置: {MYSQL_CONFIG}")

        # 使用pymysql建立数据库连接
        connection = pymysql.connect(**MYSQL_CONFIG)
        cursor = connection.cursor()
        app.logger.info("成功连接到数据库")

        # 使用参数化查询防止SQL注入
        sql = """
        SELECT
            `航班班次`,
            `机型`,
            `出发城市`,
            `到达城市`,
            `航空公司`,
            `起飞机场`,
            `降落机场`,
            `起飞时间`,
            `降落时间`,
            `价格_元`,
            `飞行时长_分钟`
        FROM flight_data
        WHERE
            `航班班次` LIKE %s
            OR `出发城市` LIKE %s
            OR `到达城市` LIKE %s
            OR `机型` LIKE %s
        """

        # 构建模糊搜索参数
        search_pattern = f"%{query}%"
        cursor.execute(sql, (search_pattern, search_pattern, search_pattern, search_pattern))
        results = cursor.fetchall()
        app.logger.info(f"搜索结果数量: {len(results)}")
        return jsonify(results)
#
    except pymysql.Error as err:  # 捕获pymysql特定错误
        # 详细区分数据库错误类型
        error_code = err.args[0]
        app.logger.error(f"数据库操作错误: {err}")

        if error_code == 1045:  # 访问被拒绝（用户名/密码错误）
            app.logger.error("错误原因: 数据库用户名或密码错误，请检查MYSQL_CONFIG配置")
            error_msg = "数据库认证失败，请检查配置"
        elif error_code == 1049:  # 数据库不存在
            app.logger.error("错误原因: 数据库不存在，请确认airline_data数据库已创建")
            error_msg = "目标数据库不存在"
        elif error_code == 2003:  # 无法连接到服务器
            app.logger.error("错误原因: 无法连接到数据库服务器，请检查MySQL服务是否运行")
            error_msg = "数据库服务器连接失败"
        elif error_code == 1146:  # 表不存在
            app.logger.error("错误原因: flight_data表不存在，请确认表结构")
            error_msg = "数据表不存在"
        else:
            error_msg = f"数据库操作失败: {str(err)}"

        return jsonify({"error": error_msg}), 500

    except Exception as e:
        # 捕获其他未知异常（如网络中断、驱动问题等）
        app.logger.error(f"未知错误: {str(e)}", exc_info=True)
        return jsonify({"error": f"系统异常: {str(e)}"}), 500

    finally:
        # 确保连接和游标被正确关闭（即使发生异常）
        if cursor:
            try:
                cursor.close()
            except Exception:
                app.logger.warning("关闭游标时发生异常")

        if connection and connection.open:  # pymysql使用connection.open检查连接状态
            try:
                connection.close()
                app.logger.info("数据库连接已关闭")
            except Exception:
                app.logger.warning("关闭数据库连接时发生异常")

# 启动程序
if __name__ == '__main__':
    init_db()
    app.run(debug=True)
